package com.uniquesoft.excelreader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.logging.Logger;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.uniquesoft.common.CommonUtils;
import com.uniquesoft.common.UnitPostion;
 

public class ReadExcelUtils {
	private final static Logger logger = Logger.getLogger(ReadExcelUtils.class.getName());
    private Workbook wb;
    private Sheet sheet;
    private Row row;
 
    public ReadExcelUtils(String filepath) {
    	logger.info("Init ReadExcelUtils based filepath ::: "+filepath);
        if(filepath==null){
            return;
        }
        String ext = filepath.substring(filepath.lastIndexOf("."));
        try {
            InputStream is = new FileInputStream(filepath);
            if(".xls".equals(ext)){
                wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(ext)){
                wb = new XSSFWorkbook(is);
            }else{
                wb=null;
            }
        } catch (FileNotFoundException e) {
        	e.printStackTrace();
        } catch (IOException e) {
        	e.printStackTrace();
        }
    }

 
    /**
     * 读取Excel数据内容
     * 
     * @param InputStream
     * @return Map 包含单元格数据内容的Map对象
     */
	public Map<Integer, Map<Integer, Object>> readExcelContent() throws Exception {
      if(wb==null){
    	  throw new Exception("Workbook对象为空！");
      }
      
      Map<Integer, Map<Integer, Object>> content = new HashMap<Integer, Map<Integer, Object>>();
      int sheetNum = wb.getNumberOfSheets();
      int sheetRowNum = 0;
      logger.info("Numbers of Sheets :::"+sheetNum);
      for(int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
    	  sheet = wb.getSheetAt(sheetIndex);
    	  
    	  List<CellRangeAddress> CellRAsList = new ArrayList<CellRangeAddress>();
    	  int startRowIndex = sheet.getFirstRowNum(), endRowIndex = sheet.getLastRowNum();
    	  System.out.println("startRowIndex:"+ startRowIndex+ ":endRowIndex:"+endRowIndex);
    	  row = sheet.getRow(startRowIndex);
    	  if (row!= null){
	    	  int colNum = row.getPhysicalNumberOfCells();
	    	  System.out.println("row:"+ startRowIndex+ ":getPhysicalNumberOfCells:"+colNum);
	    	  short startIndex = row.getFirstCellNum(), endIndex = row.getLastCellNum();
	    	  System.out.println("cell:getFirstCellNum : "+ startIndex+ ":getLastCellNum:"+endIndex);
	    	  CellRAsList = getCellRawRangeAddresses(sheet, startRowIndex, endRowIndex, startIndex, endIndex);
	    	  for(int j = startRowIndex; j <= endRowIndex; j++ ) {
	    		  row = sheet.getRow(j);
	    		  if (row != null){
	    			  System.out.println("enter get row "+j);
	    			  Map<Integer, Object> rowContent = getCominbedValue(sheet, row, colNum , startIndex, CellRAsList);
	    			  if(!CommonUtils.isMapBlank(rowContent)){
	    				  content.put(j+sheetRowNum, rowContent);
	    			  }
	    		  }
	    	  }
	
	    	  sheetRowNum = sheetRowNum + endRowIndex;
    	  }
      }
      
      return content;
	}

	
    /**
     * 
     * 根据Cell类型设置数据
     * 
     * @param cell
     * @return
     * @throws ParseException 
     */
	@SuppressWarnings("deprecation")
	private static Object getCellValue(Cell cell) throws ParseException { 
    	
    	Object obj = null; 
    	if(cell == null) return null; 
    	
    	switch (cell.getCellType()) { 
	    case Cell.CELL_TYPE_STRING: 
	        obj = cell.getRichStringCellValue().getString().replaceAll("\\s{1,}", " ");
	        obj = CommonUtils.StringToDate(obj.toString());
	        break; 
	    case Cell.CELL_TYPE_NUMERIC: 
	        if (DateUtil.isCellDateFormatted(cell)) { 
	            obj = cell.getDateCellValue(); 
	        } else { 
	            obj = cell.getNumericCellValue(); 
	        } 
	        break; 
	    case Cell.CELL_TYPE_BOOLEAN: 
	        obj =  cell.getBooleanCellValue(); 
	        break; 
	    case Cell.CELL_TYPE_FORMULA: 
	        obj = cell.getCellFormula(); 
	        break; 
	    default: 
    } 
    return obj; 
}

    
    /** 
    * 获取指定合并区域 
    * @param sheet1 
    * @param startIndex 
    * @param endIndex 
    * @param indexes
    * @return 
    */    
    public static List<CellRangeAddress> getCellRawRangeAddresses(Sheet sheet1, 
    	int startRowIndex, int endRowIndex, short startIndex, short endIndex){ //indexes, excel column indexes 
    	
    	List<CellRangeAddress> resultList = new ArrayList<CellRangeAddress>();
    	System.out.println("获取该sheet中合并单元格的个数："+sheet1.getNumMergedRegions());
        for (int i = 0; i < sheet1.getNumMergedRegions(); i++) { 
            CellRangeAddress cellRA = sheet1.getMergedRegion(i); 
            int firstRow = cellRA.getFirstRow(); 
            int lastRow =cellRA.getLastRow(); 
            int firstCol = cellRA.getFirstColumn(); 
            int lastCol = cellRA.getLastColumn();   
            System.out.println("每一个合并单元格的位置：起始行:"+firstRow+",结束行： "+ lastRow + ",起始列："+ firstCol+  "结束列："+ lastCol);           
            if(lastRow >= startRowIndex && firstRow <= endRowIndex){ 
                if(lastCol>= startIndex && firstCol <= endIndex) 
                resultList.add(cellRA); 
            } 
            System.out.println("共有多少个合并单元格："+resultList.size());
        } 
        return resultList; 
    }
    
    /** 
    * 获取给定行的组合值
    * @param sheet1 
    * @param row 
    * @param indexes 
    * @param limitedCellRAs
    * @return 
     * @throws ParseException 
    */ 
    public static Map<Integer, Object> getCominbedValue(Sheet sheet1, Row row, int colNum , int startColIndex,
		List<CellRangeAddress> limitedCellRAs) throws ParseException{  

    	Map<Integer, Object> rowCotent = new HashMap<Integer, Object>();  // 用以存储最终结果
		Map<UnitPostion, CellRangeAddress> theMap = 
		new LinkedHashMap<UnitPostion, CellRangeAddress>(); 
		// 记录被合并的单元格所在的合并区域
		int rowIndex = row.getRowNum(); 
		Iterator<CellRangeAddress> itcra = limitedCellRAs.iterator(); 
	    while(itcra.hasNext()){ 
	        CellRangeAddress cra = itcra.next(); 
	        int firstRow = cra.getFirstRow(); 
	        int lastRow =cra.getLastRow(); 
	        if(rowIndex>= firstRow && rowIndex<= lastRow){ 
	            int firstCol = cra.getFirstColumn(); 
	            int lastCol = cra.getLastColumn();    
	            for(int j=startColIndex; j<startColIndex + colNum;j++){ 
	                short index = (short)j; 
	                if(index >= firstCol && index <= lastCol){   
	                    theMap.put(new UnitPostion(rowIndex,index),cra); 
	                } 
	            }      
	        } 
	    } 
	    // 记录合并单元格所对应的列号
	    Iterator<UnitPostion> itpos = theMap.keySet().iterator(); 
	    Set<Short> compIndexes = new HashSet<Short>(); 
	    while(itpos.hasNext()){ 
	    	UnitPostion cellPos = itpos.next(); 
	        int colIndex = cellPos.getCol(); 
	        compIndexes.add((short)colIndex);
	    } 
	    List<UnitPostion> cachedList =new ArrayList<UnitPostion>(); 
	    for(int i =startColIndex;i< startColIndex + colNum;i++){ 
	        if(!compIndexes.contains(i)){ 
	           // 如果不包含此列号，则对应的 cell 不是一个合并单元格，调用清单 2 中的方法获取其值
	            Cell cell = row.getCell(i); 
	            Object result= getCellValue(cell); 
	            if(!CommonUtils.checkNull(result)) 
	            	rowCotent.put(i, result);
	        }else{  
	        	CellRangeAddress cellRA = theMap.get(new UnitPostion (rowIndex, i)); 
	            if(cellRA != null){ 
	                int cellrow = cellRA.getFirstRow(); 
	                int cellcol = cellRA.getFirstColumn(); 
	                UnitPostion cPos = new UnitPostion(cellrow,cellcol); 
	                //If it is Not the cached CellRangeAddress object 
	                if(!cachedList.contains(cPos)){ 
	                    cachedList.add(cPos); // 放到缓存中，如果是同一个合并单元格，我们取其值一次
	                    Object value = cellRA.formatAsString().replaceAll("\\s*", "");
		            	rowCotent.put(i, value);
	                } 
	            } 
	        } 
	    } 
	    return rowCotent; 
	}

}